package org.shj.plugin.tdpl.gen.generator.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.maven.plugin.MojoExecutionException;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.shj.plugin.tdpl.gen.generator.Generator;
import org.shj.tdpl.common.constant.TdplConstants;
import org.shj.plugin.tdpl.gen.db.AbstractDbConnect;
import org.shj.plugin.tdpl.gen.to.ColumnTO;
import org.shj.tdpl.common.utils.StringUtil;

/**
 * an abstract class for generating excel file
 *
 * @author huangjian
 *
 */
@Slf4j
@Getter
@Setter
public abstract class AbstractXlsxGenerator implements Generator {

	/**
	 * The folder where the file will be generated in.
	 */
	protected String generatedFilePath;

	/**
	 * Database connect which is used to get table metadata.
	 */
	protected AbstractDbConnect dbConnect;
	
	protected boolean isUpdate = false;
	
	//Some common style

	protected XSSFFont headFont;
	protected XSSFCellStyle contentCs;
	protected XSSFCellStyle headStyle;
	protected XSSFCellStyle linkStyle;
	protected XSSFCellStyle pkcs;

	/**
	 * 每个表有哪些列
	 */
	protected Map<String, List<ColumnTO>> tableMap;
	
	private String sortedTables;
	private Map<String, String> updateFkColMap;
	
	private Map<String,List<String[]>> nullabeFkParentChildTables;

	/**
	 * the max length of sheet name in excel is 31, the length of table name may be larger than 31.
	 * key is sheetName, value is tableName
	 */
	protected Map<String, String> sheetNameTableMap = new HashMap<>();

	protected Map<String, String> tableSheetNameMap = new HashMap<>();

	@Override
	public void generateSchema() throws Exception {
		File file = new File(generatedFilePath);
		File[] list = file.listFiles();
		//1. Rename old files
		if(isUpdate){
			String fileName;
			File tmp;
			for(File f : list){
				if(f.isDirectory()){
					//only process file, ignore directory
					continue;
				}
				fileName = f.getAbsolutePath();
				int ind = fileName.lastIndexOf(".");
				tmp = new File(fileName.substring(0,ind)+"_tmp" + fileName.substring(ind));
				FileUtils.moveFile(f, tmp);
			}
		}

		//2. generate new file
		generate();

		//remove temp files
		if(isUpdate){
			list = file.listFiles();
			for(File f : list){
				if(f.getName().endsWith("_tmp.xlsx")){
					f.delete();
				}
			}
		}
	}

	/**
	 * Generate the Excel schema in the folder
	 * @throws Exception
	 */
	protected abstract void generate() throws Exception;

	public void init(String generatedFilePath, AbstractDbConnect dbConnect) throws MojoExecutionException {
		if(generatedFilePath == null || "".equals(generatedFilePath.trim())){
			String msg = "Please define the path of generated schema file.";
			log.error(msg);
			throw new MojoExecutionException(msg);
		}

		this.generatedFilePath = generatedFilePath;

		File folder = new File(generatedFilePath);
		if(folder.isFile()){
			String msg = "The file path should be a folder.";
			log.error(msg);
			throw new MojoExecutionException(msg);
		}

		if(folder.exists()){
			isUpdate = folder.list().length > 0;
		}else{
			if(folder.mkdirs()){
				throw new MojoExecutionException("Create folder(" + generatedFilePath +") failed.");
			}
			isUpdate = false;
		}

		tableMap = dbConnect.retrieveTableColumnInfo();
		int maxLengthOfSheetName = 31;
		for(String tableName : tableMap.keySet()){
			String sheetName = tableName;
			if(tableName.length() > maxLengthOfSheetName){
				sheetName = tableName.substring(tableName.length() - maxLengthOfSheetName);
				if(sheetName.startsWith("_")){
					sheetName = sheetName.substring(1);
				}
			}
			sheetNameTableMap.put(sheetName, tableName);
			tableSheetNameMap.put(tableName, sheetName);
		}


		sortedTables = dbConnect.retrieveSortedTables();
		// updateFkColMap 在 dbConnect.retrieveSortedTables(); 方法中被初始化
		updateFkColMap = dbConnect.getUpdateFkColMap();
		this.dbConnect = dbConnect;

		// updateFkColMap 在 dbConnect.retrieveSortedTables(); 方法中被初始化
		Map<String,List<String[]>> nullabeFkChildParentTables = dbConnect.getNullabeFkChildParentTables();
		nullabeFkParentChildTables = new HashMap<>(8);

		for(String child : nullabeFkChildParentTables.keySet()){
			List<String[]> parents = nullabeFkChildParentTables.get(child);
			for(String[] parent : parents){
				if(!nullabeFkParentChildTables.containsKey(parent[0])){
					nullabeFkParentChildTables.put(parent[0], new ArrayList<>());
				}
				List<String[]> childs = nullabeFkParentChildTables.get(parent[0]);
				String[] childInfo = new String[2];
				childInfo[0] = child;
				childInfo[1] = parent[1];
				childs.add(childInfo);
			}
		}
	}

	/**
	 * Get the tableName-SqlIndexColumnNumber map.
	 * Key is the table name, value is the column number of the generated sql in the table sheet
	 *
	 * @param indSheet Index Sheet
	 * @return
	 */
	protected Map<String,Integer> getOldSqlColNumMap(XSSFSheet indSheet){
		// key - table name;
		Map<String,Integer> oldColNumMap = new HashMap<>();
		XSSFRow row = null;

		String tblNm = null;
		XSSFCell cell = null;

		for(int i = 1 ; i <= indSheet.getLastRowNum(); i++){
			row = indSheet.getRow(i);
			cell = row.getCell(0); //table name column
			if(cell == null){
				continue;
			}
			tblNm = cell.getStringCellValue();

			cell = row.getCell(1); //sql column index
			Integer sqlIndex = cell == null ? null : (int)cell.getNumericCellValue();
			if(tblNm != null && !"".equals(tblNm.trim())){
				oldColNumMap.put(tblNm.trim().toUpperCase(), sqlIndex);
			}
		}

		return oldColNumMap;
	}

	/**
	 * Get the Staff-Series map.
	 * key is the Staff Name, value is the range of data version which the staff can use. It used to avoid the
	 * different staff prepare the same version of test data. The same version of test data will be loaded for the test case.
	 * If they prepared the version, the test result may be failed.
	 * @param indSheet Index Sheet
	 * @return
	 */
	protected Map<String,String> getOldStaffSeriesMap(XSSFSheet indSheet){
		Map<String,String> staffMap = new LinkedHashMap<String,String>();
		XSSFRow row ;
		String staffNm;
		XSSFCell cell;

		for(int i = 1 ; i <= indSheet.getLastRowNum(); i++){
			row = indSheet.getRow(i);
			//table name column
			cell = row.getCell(0);
			if(cell == null){
				continue;
			}

			cell = row.getCell(4);
			staffNm = cell == null ? null : cell.getStringCellValue();

			cell = row.getCell(5);
			String series = cell == null ? null : cell.getStringCellValue();
			if(staffNm != null && !"".equals(staffNm.trim())){
				staffMap.put(staffNm, series);
			}
		}
		return staffMap;
	}

	/**
	 * Get version-tables map.
	 * key is the version of test data, value is the tables that has the version of test data.
	 * E.g. Version is 1000, tables are "Table1;Table2". This means that the test case need the
	 * test data which version is 1000 in Table1 and Table2.
	 *
	 * @param oldBook
	 * @return
	 */
	protected Map<Integer,String> getOldVersionTablesMap(XSSFWorkbook oldBook){
		XSSFSheet sheet = oldBook.getSheetAt(1);
		Map<Integer,String> versionTablesMap = new HashMap<>();
		XSSFRow row = null;
		XSSFCell cell = null;

		if(sheet != null){
			Integer version = 0;
			String tables = null;
			for(int i = 1 ; i <= sheet.getLastRowNum(); i++){
				row = sheet.getRow(i);
				cell = row.getCell(0);
				if(cell != null){
					if(cell.getCellType() == Cell.CELL_TYPE_STRING){
						if(StringUtil.isEmpty(cell.getStringCellValue())){
							continue;
						}else{
							version = Integer.parseInt(cell.getStringCellValue());
						}
					}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
						version = (int)cell.getNumericCellValue();
					}
				}

				cell = row.getCell(1);
				tables = cell == null ? "" : cell.getStringCellValue();

				if(!"".equals(tables.trim())){
					versionTablesMap.put(version, tables);
				}
			}
		}
		return versionTablesMap;
	}


	protected XSSFSheet generateNewSheet(XSSFWorkbook book, String tableName){
		XSSFSheet sheet = book.createSheet(tableSheetNameMap.get(tableName));
		XSSFRow colNmRow = sheet.createRow(0);
		XSSFRow colTypeRow = sheet.createRow(1);
		XSSFRow nullableRow = sheet.createRow(2);
		XSSFRow contentRow = sheet.createRow(TdplConstants.FIRST_CONTENT_ROW_IND);

		//1. generate 3 header row
		XSSFCell c1 = colNmRow.createCell(0);
		c1.setCellValue("Column Name");
		c1.setCellStyle(headStyle);
		XSSFCell c2 = colTypeRow.createCell(0);
		c2.setCellValue("Data Type");
		c2.setCellStyle(headStyle);
		XSSFCell c3 = nullableRow.createCell(0);
		c3.setCellValue("Mandatory");
		c3.setCellStyle(headStyle);
		contentRow.createCell(0).setCellStyle(contentCs);
		contentRow.getCell(0).setCellType(XSSFCell.CELL_TYPE_STRING);

		sheet.autoSizeColumn(0);
		List<ColumnTO> columns = tableMap.get(tableName);
		//the db column info is from B column in excel
		int col = 1;

		String type ;
		for(ColumnTO colTo : columns){
			String nmStr = colTo.getName();
			type = colTo.getType().toUpperCase();
			String typeLen = type + (!type.contains("CHAR") ? "" : "("+colTo.getLength()+")");
			XSSFCell nmCell = colNmRow.createCell(col);
			nmCell.setCellValue(nmStr);
			nmCell.setCellStyle(headStyle);

			XSSFCell typeCell = colTypeRow.createCell(col);
			typeCell.setCellValue(typeLen);
			typeCell.setCellStyle(headStyle);

			XSSFCell nullCell = nullableRow.createCell(col);
			String nullable = colTo.getNullable();
			nullCell.setCellValue("Y".equals(nullable)?"N" : "Y");
			nullCell.setCellStyle(headStyle);
			contentRow.createCell(col).setCellStyle(contentCs);
			contentRow.getCell(col).setCellType(XSSFCell.CELL_TYPE_STRING);

			sheet.autoSizeColumn(col);
			col++;
		}

		setUpdateSubTableFkToNullBeforeDeleteParentTablSql(sheet,tableName);

		return sheet;
	}

	private void setUpdateSubTableFkToNullBeforeDeleteParentTablSql(XSSFSheet sheet, String tableName){
		if(nullabeFkParentChildTables.containsKey(tableName)){
			//generate update FK to null sql before delete current table
			List<String[]> nullableSubTblInfos = nullabeFkParentChildTables.get(tableName);
			XSSFRow row = sheet.getRow(TdplConstants.UPDATE_BEFORE_DEL_ROW_IND);

			int delSqlColIndex = tableMap.get(tableName).size() + 2;
			XSSFCell cell = row.createCell(delSqlColIndex - 1);
			cell.setCellValue(" ");

			for(String[] subInfo : nullableSubTblInfos){
				StringBuilder sb = new StringBuilder();
				sb.append("Update ").append(subInfo[0]).append(" set ");

				String[] fks = subInfo[1].split(TdplConstants.SEPARATE);
				for(int i = 0 ; i < fks.length; i++){
					if(i > 0){
						sb.append(", ");
					}
					sb.append(fks[i]).append(" = NULL");
				}
				sb.append(";");

				cell = row.createCell(delSqlColIndex);
				cell.setCellStyle(contentCs);
				cell.setCellValue(sb.toString());
				delSqlColIndex++;
			}
		}
	}

	/**
	 * Generate sql formula for each row
	 * @param sheet
	 * @param lastRow
	 */
	protected  void generateSqlCells(XSSFSheet sheet, int lastRow){
		String sheetName = sheet.getSheetName();
		String tableName = sheetNameTableMap.get(sheetName);
		List<ColumnTO> columns = tableMap.get(tableName);
		log.debug("Begin generate DELETE, UPDATE and INSERT sql formula in " + sheetName + " sheet.");
		
		int delSqlColIndex = columns.size() + 2;
		
		for(int rowIndex = TdplConstants.FIRST_CONTENT_ROW_IND; rowIndex <= lastRow ; rowIndex++){
			int rowLbl = rowIndex + 1;
			//generate delete sql
			StringBuilder delSql = new StringBuilder();
			delSql.append("\"DELETE FROM ").append(tableName);
			
			StringBuilder where = new StringBuilder();
			
			//generate insert sql
			StringBuilder colNm = new StringBuilder();
			StringBuilder values = new StringBuilder();
			int col = 1;
			for(ColumnTO colTo : columns){
				String type = colTo.getType().toUpperCase();
				if(type.indexOf("LOB") == -1 && type.indexOf("LONG") == -1 && type.indexOf("TEXT") == -1 
						&& !isNeedUpdateColumn(tableName, colTo.getName())){
					String cellNm = convertNum2Col(col,rowLbl);
					colNm.append("IF(").append(cellNm).append("=\"\",\"\",\"").append(colTo.getName()).append(",\")&");
					
					values.append(dbConnect.genValueForInsertSql(type, cellNm));
					
					//for delete and update sql
					if("Y".equalsIgnoreCase(colTo.getIsPk())){
						if(where.indexOf("WHERE") == -1){
							where.append(" WHERE ");
						}else{
							where.append(" AND ");
						}
						where.append(colTo.getName()).append("=").append(dbConnect.genEqValue(type, cellNm));
						
					}
				}//end if
				col++;
			}//end columns

            XSSFRow xssfRow = sheet.getRow(rowIndex);
			if(where.length() > 1 && xssfRow != null){
				delSql.append(where).append(";\"");
				XSSFCell delCell = xssfRow.createCell(delSqlColIndex);
				delCell.setCellFormula(delSql.toString());
				
				//generate update sql 
				boolean needGenUpdateSql = updateFkColMap.containsKey(tableName);
				if(needGenUpdateSql){
					//used for update circle FK after insert
					StringBuilder updateAfterInsertSql = new StringBuilder();

					StringBuilder ifNullSql = new StringBuilder();
					StringBuilder setSqlForInsert = new StringBuilder();
					StringBuilder setSqlForDelete = new StringBuilder();
					String[] fkCols = updateFkColMap.get(tableName).split(TdplConstants.SEPARATE);
					
					for(int n = 0 ; n < fkCols.length; n++){
						String fkCol = fkCols[n].trim();
						for(int i = 0 ; i < columns.size() ; i++){
							if(fkCol.equalsIgnoreCase(columns.get(i).getName())){
								String cellNm = convertNum2Col(i + 1, rowLbl);
								if(n > 0){
									ifNullSql.append(",");
									setSqlForInsert.append("&");
									setSqlForDelete.append(", ");
								}
								ifNullSql.append(cellNm).append("=\"\"");
								setSqlForInsert.append("IF(").append(cellNm).append("=\"\",\"\",\"").append(fkCol).append("=")
								      .append(dbConnect.genEqValue(columns.get(i).getType(), cellNm)).append(", \")");
								
								setSqlForDelete.append(fkCol).append(" = NULL ");
							}
						}
					}
					
					updateAfterInsertSql.append("IF(");
					if(fkCols.length > 1){
						updateAfterInsertSql.append("AND(");
					}
					updateAfterInsertSql.append(ifNullSql);
					if(fkCols.length > 1){
						updateAfterInsertSql.append(")");
					}
					
					int updateSetCellInd = delSqlColIndex + 3;
					XSSFCell updateSetCell = sheet.getRow(rowIndex).createCell(updateSetCellInd);
					updateSetCell.setCellFormula(setSqlForInsert.toString());
					String cellNm = convertNum2Col(updateSetCellInd, rowLbl);
					
					updateAfterInsertSql.append(", \"\", \"UPDATE ").append(tableName)
			                 .append(" SET \"&LEFT(").append(cellNm).append(",LEN(").append(cellNm).append(")-2)&\" ") // -2: remove the last ", " in the setsql part
			                 .append(where).append(";\")");
					XSSFCell updateCell = sheet.getRow(rowIndex).createCell(delSqlColIndex + 2);
					updateCell.setCellFormula(updateAfterInsertSql.toString());

				}
			}
			log.debug("Generate INSERT sql formula in row " + rowIndex + ".");
			generateInsertSql(sheet,colNm.deleteCharAt(colNm.length()-1).toString(),
					values.deleteCharAt(values.length()-1).toString(), delSqlColIndex + 4, rowIndex);
		}
	}
	
	private boolean isNeedUpdateColumn(String table, String colNm){
		if(updateFkColMap.containsKey(table)){
			for(String fk : updateFkColMap.get(table).split(TdplConstants.SEPARATE)){
				if(colNm.equals(fk)){
					return true;
				}
			}
		}
		return false;
	}
	

	/**
	 * the length of character in formula should be less than 256. If there are many columns in table,
	 * it's impossible to write the insert sql in one formula.
	 * @param sheet
	 * @param columns
	 * @param values
	 * @param insertSqlColIndex
	 * @param rowNm
	 */
	protected void generateInsertSql(XSSFSheet sheet,String columns,String values,int insertSqlColIndex,int rowNm){		
		String sheetName = sheet.getSheetName();
		String tableNm = sheetNameTableMap.get(sheetName);
		/*  insertSqlColIndex     insertSqlColIndex+1      insertSqlColIndex+2         
		 *  final sql             column names             values of columns
		 */
		int rowLbl = rowNm + 1;
		XSSFRow row = sheet.getRow(rowNm);
		if(row == null){
			row = sheet.createRow(3);
		}
		String colString1 = "\"INSERT INTO "+ tableNm + " (\"";
		String colString2 = "\") VALUES (\"";
		
		List<String> colPos = new ArrayList<String>();
		int i = insertSqlColIndex + 3;
		
		StringBuilder sb = new StringBuilder();
		//begin get insertSqlColIndex+1
		String[] arr = columns.split("&");
		for(String s : arr){
			sb.append(s).append("&");
			if(sb.length() > 200){
				sb.deleteCharAt(sb.length()-1);
				XSSFCell f1 = row.createCell(i);
				f1.setCellFormula(sb.toString());
				sb = new StringBuilder();
				colPos.add(convertNum2Col(i,rowLbl));
				i++;
			}
		}
		if(sb.length() > 0){
			sb.deleteCharAt(sb.length()-1);
			XSSFCell f1 = row.createCell(i);
			f1.setCellFormula(sb.toString());
			sb = new StringBuilder();
			colPos.add(convertNum2Col(i,rowLbl));
			i++;
		}
		
		XSSFCell nmCell = row.createCell(insertSqlColIndex+1);
		StringBuilder fomula = new StringBuilder();
		for(String s : colPos){
			fomula.append(s).append("&");
		}
		fomula.deleteCharAt(fomula.length()-1);
		nmCell.setCellFormula(fomula.toString());
		colPos.clear();
		//end get col+1
		
		//begin get col+2		
		arr = values.split(TdplConstants.SEPARATE);
		for(String s : arr){
			sb.append(s).append("&");
			if(sb.length() > 200){
				sb.deleteCharAt(sb.length()-1);
				XSSFCell f1 = row.createCell(i);
				f1.setCellFormula(sb.toString());
				sb = new StringBuilder();
				colPos.add(convertNum2Col(i,rowLbl));
				i++;
			}
		}
		if(sb.length() > 0){
			sb.deleteCharAt(sb.length()-1);
			XSSFCell f1 = row.createCell(i);
			f1.setCellFormula(sb.toString());
			sb = new StringBuilder();
			colPos.add(convertNum2Col(i,rowLbl));
			i++;
		}
		
		XSSFCell varCell = row.createCell(insertSqlColIndex+2);
		fomula = new StringBuilder();
		for(String s : colPos){
			fomula.append(s).append("&");
		}
		fomula.deleteCharAt(fomula.length()-1);
		varCell.setCellFormula(fomula.toString());
		//end get col+2		
		
		//set final fomula
		XSSFCell fomulaCell = row.createCell(insertSqlColIndex);
		fomula = new StringBuilder();
		String nameCol = convertNum2Col(insertSqlColIndex+1,rowLbl);
		String varCol = convertNum2Col(insertSqlColIndex+2,rowLbl);
		fomula.append(colString1).append("&LEFT(")
		      .append(nameCol).append(",LEN(").append(nameCol).append(")-1)")  // -1: Remove the last ","
		      .append("&").append(colString2)
		      .append("&LEFT(").append(varCol).append(",LEN(").append(varCol).append(")-1)")
		      .append("&\");\"");
		fomulaCell.setCellFormula(fomula.toString());
		
	}

	protected void initFontAndStyle(XSSFWorkbook book){
		headFont = book.createFont();
		headFont.setFontHeightInPoints((short)9);
		headFont.setFontName("Arial");
		
		contentCs = book.createCellStyle();
		contentCs.setFont(headFont);
		contentCs.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		contentCs.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		contentCs.setBorderRight(XSSFCellStyle.BORDER_THIN);
		contentCs.setBorderTop(XSSFCellStyle.BORDER_THIN);
		contentCs.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		contentCs.setDataFormat((short)0x31); //set the cell as string type	
		
		headStyle = book.createCellStyle();
		headStyle.setFont(headFont);
		headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		
		linkStyle = book.createCellStyle();
		XSSFFont linkFont = book.createFont();
		linkFont.setUnderline(XSSFFont.U_SINGLE);
		linkFont.setColor(IndexedColors.BLUE.getIndex());
		linkFont.setFontHeightInPoints((short)9);
	    linkStyle.setFont(linkFont);
	    linkStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
	    linkStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
	    linkStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
	    linkStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
	    
	    pkcs = book.createCellStyle();
	    pkcs.setFont(headFont);
	    pkcs.setBorderBottom(XSSFCellStyle.BORDER_THIN);
	    pkcs.setBorderLeft(XSSFCellStyle.BORDER_THIN);
	    pkcs.setBorderRight(XSSFCellStyle.BORDER_THIN);
	    pkcs.setBorderTop(XSSFCellStyle.BORDER_THIN);
	    pkcs.setAlignment(XSSFCellStyle.ALIGN_LEFT);
	}
	
	/**  col      char     ascii
	 *    1        B       66
	 *    2        C       67
	 *    ...
	 *    25       Z       90
	 *    26       AA      65+65
	 *    27       AB      65+66
	 *    ...
	 *    51       AZ      65+90
	 *    52       BA      66+65
	 *    
	 *    if col = 1 and rowLbl = 5, return "C5", it means the cell in the excel is "C5"
	 */
	protected String convertNum2Col(int col,int rowLbl){
		StringBuilder str = new StringBuilder();
		char ch;		
		int shan = col/26;
		int yushu = col%26;
		
		if(shan != 0){
			ch = (char)(shan+64);
			str.append(ch);
		}
		ch = (char)(yushu+65);
		str.append(ch);
		str.append(rowLbl+"");
		return str.toString();
	}
	
	protected void fillInfoToIndexSheet(XSSFSheet indSheet, Map<String,String> staffMap, boolean genExcelForEachTable){
		XSSFWorkbook book = indSheet.getWorkbook();
		XSSFCreationHelper createHelper = book.getCreationHelper();
	    
		XSSFRow headerRow = indSheet.createRow(0);		
		createHeaderCell(headerRow, 0, "Table Name");
		createHeaderCell(headerRow, 1, "Sql Column Index");
		createHeaderCell(headerRow, 2, "Primary Key Column");
		createHeaderCell(headerRow, 4, "Staff Name");
		createHeaderCell(headerRow, 5, "Begin Series");
		createHeaderCell(headerRow, TdplConstants.SORTED_TABLE_COL_IND, "Sorted Tables");
	    
	    int i = 1 ;
		XSSFCell cell = null;
		XSSFHyperlink link = null;
		XSSFCell colNumCell = null;
		XSSFCell pkCell = null;
		XSSFRow row = null;
		
		for(String key : tableMap.keySet()){
			row = indSheet.createRow(i);
			//create table name hyperlink
			cell = row.createCell(0);
			cell.setCellValue(key);

			if(genExcelForEachTable){
				link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
				link.setAddress(tableSheetNameMap.get(key) + ".xlsx");
			}else{
				link = createHelper.createHyperlink(XSSFHyperlink.LINK_DOCUMENT);
				link.setAddress("'" + tableSheetNameMap.get(key) + "'!A1");
			}
			
			cell.setHyperlink(link);
			cell.setCellStyle(linkStyle);
			
			//create number of table's columns. used for getting the insert SQL position
			colNumCell = row.createCell(1);
			// 2 - the version column(first column) + empty one column
			colNumCell.setCellValue(tableMap.get(key).size() + 2);
			colNumCell.setCellStyle(contentCs);
			
			//create pk columns of the table
			pkCell = row.createCell(TdplConstants.PK_COL_IND);
			pkCell.setCellValue(getPkColumns(key));
			pkCell.setCellStyle(pkcs);
					
			i++;
		}
		
		if(isUpdate){ // add the staff name info
			String series = null;
			i = 1;
			for(String staffNm : staffMap.keySet()){
				row = indSheet.getRow(i);
				if(row == null){
					row = indSheet.createRow(i);
				}
				
				cell = row.createCell(4); //staff name column
				cell.setCellValue(staffNm);
				cell.setCellStyle(pkcs);
				
				series = staffMap.get(staffNm);
				if(series != null){
					cell = row.createCell(5); //begin series column
					cell.setCellValue(series);
					cell.setCellStyle(pkcs);
				}
				
				i++;
			}
		}
		
		//create sortedTable Cell
		cell = indSheet.getRow(0).createCell(TdplConstants.SORTED_TABLE_COL_IND + 1); //put it behind "Sorted Tables"
		cell.setCellStyle(pkcs);
		cell.setCellValue(sortedTables);
		
		addNotePart(indSheet);
		
		indSheet.autoSizeColumn(0);
		indSheet.autoSizeColumn(1);
		indSheet.autoSizeColumn(2);
		indSheet.autoSizeColumn(4);
		indSheet.autoSizeColumn(5);
		indSheet.autoSizeColumn(7);
		
	}
	
	protected void createVersionTableMapSheet(XSSFWorkbook book, Map<Integer, String> versionTablesMap){
		XSSFSheet sheet = book.createSheet(TdplConstants.VERION_TABLE_MAP_SHEET_NAME);
		XSSFRow headerRow = sheet.createRow(0);
		createHeaderCell(headerRow, 0, "Data Version");
		createHeaderCell(headerRow, 1, "Table Names");
		
		int rowInd = 1;
		XSSFRow row = null;
		XSSFCell cell = null;
		for(Integer version : versionTablesMap.keySet()){
			row = sheet.createRow(rowInd);
			cell = row.createCell(0);
			cell.setCellValue(version);
			
			cell = row.createCell(1);
			cell.setCellValue(versionTablesMap.get(version));
			rowInd++;
		}
		
		sheet.autoSizeColumn(0);
		sheet.autoSizeColumn(1);
	}
	
	private String getPkColumns(String table){
		List<ColumnTO> columns = tableMap.get(table);
		StringBuilder pks = new StringBuilder();
		for(ColumnTO ct : columns){
			if("Y".equalsIgnoreCase(ct.getIsPk())){
				pks.append(ct.getName()).append(",");
			}
		}
		if(pks.indexOf(",") != -1){
			pks.deleteCharAt(pks.length()-1);
		}
		return pks.toString();
	}
	
	private void addNotePart(XSSFSheet indSheet){
		int noteRowInd = 2;
		XSSFRow row = indSheet.getRow(noteRowInd); 
		if(row == null){
			row = indSheet.createRow(2);
		}
				
		XSSFCell cell = row.createCell(TdplConstants.SORTED_TABLE_COL_IND);  //put the "Note" at the same column of "Sorted Tables"
		cell.setCellStyle(headStyle);
		cell.setCellValue("Note");
		
		createCell(indSheet, noteRowInd,      "1. 不要更改本 sheet 的任何值(除了 \"Staff Name\" 和 \"Begin Series\" 列) 。");
		createCell(indSheet, noteRowInd + 1,  "2. \"Staff Name\" 和 \"Begin Series\" 列用于给开发人员分配准备数据的序号范围。如： ");
		createCell(indSheet, noteRowInd + 2,  "     沈浪 1~1000 表示开发人员沈浪准备测试数据的序号范围为 1~1000。");
		createCell(indSheet, noteRowInd + 3,  "3. 对于 \"data type\" 列是 DATETIME 或 Timestamps 的类型, 格式应为 \"YYYY-MM-DD H24:mm:SS\" ");
		createCell(indSheet, noteRowInd + 4,  "    如果是 DATE 类型, 使用数据格式 \"YYYY-MM-DD\"(如果数据库是mssql，使用 \"mm/dd/yyyy\" ).");
		createCell(indSheet, noteRowInd + 5,  "4. 自动生成SQL的公式仅生成在第 4 行。 在其它行准备数据时，请复制整个第4行。 ");
		createCell(indSheet, noteRowInd + 6,  "5. 如果某个测试用例需要多个表的数据，对这个用例的不同sheet的数据版本列的值应一样。");
		createCell(indSheet, noteRowInd + 7,  "6. 需要指定 \"Version Table Map\" sheet 。");
		createCell(indSheet, noteRowInd + 8,  "     如： 某用例需要在 TABLE_1 和 TABLE_2 两个表中准备数据，定义数据版本为 100，则");
		createCell(indSheet, noteRowInd + 9,  "     你应该在 \"Version Table Map\" 这个 sheet 增加记录如下：");
		createCell(indSheet, noteRowInd + 10,  "     \"Data Version\" 列值为 100，\"Table Names\" 列值为 \"TABLE_1;TABLE_2\".");
		createCell(indSheet, noteRowInd + 11,  "     并且在 TABLE_1 和 TABLE_2 这两个 sheet中的第一列中，有值为 100 的数据。");
	}
	
	private void createHeaderCell(XSSFRow headerRow, int index, String value){
		XSSFCell headerCell = headerRow.createCell(index);
	    headerCell.setCellStyle(headStyle);
	    headerCell.setCellValue(value);
	}
	
	private void createCell(XSSFSheet indSheet, int rowInd, String value){
		XSSFRow row = indSheet.getRow(rowInd);
		if(row == null){
			row = indSheet.createRow(rowInd);
		}
		
		XSSFCell cell = row.createCell(TdplConstants.SORTED_TABLE_COL_IND + 1);
		cell.setCellValue(value);
	}
	
	protected  void moveOldValue2NewTable(XSSFWorkbook oldBook, Map<String,Integer> oldColNumMap,
			XSSFSheet newSheet,int newSheetColNumber, XSSFCellStyle contentCs){
		String sheetName = newSheet.getSheetName().trim().toUpperCase();
        log.info("processing " + sheetName);
		XSSFSheet oldSheet = oldBook.getSheet(sheetName);
		if(oldSheet == null)
			return;
		
		XSSFRow oldHeadRow = oldSheet.getRow(0);
		XSSFRow newHeadRow = newSheet.getRow(0);
		String tableName = sheetNameTableMap.get(newSheet.getSheetName()).toUpperCase();
		if(oldColNumMap.containsKey(tableName)){
			for(int oldCol = 0 ; oldCol < oldColNumMap.get(tableName)-1 ; oldCol++){
				String oldColNm = oldHeadRow.getCell(oldCol).getStringCellValue();
				for(int newCol = 0 ; newCol < newSheetColNumber - 1 ; newCol++){
					String newColNm = newHeadRow.getCell(newCol).getStringCellValue();
					if(oldColNm.equals(newColNm)){
						copyOld2New(oldSheet,newSheet,oldCol,newCol,contentCs);
						break;
					}
				}
			}
		}

	}
	
	private  void copyOld2New(XSSFSheet oldSheet, XSSFSheet newSheet,int oldCol, int newCol, XSSFCellStyle contentCs){
		XSSFRow xssfRow = null;
		for(int row = TdplConstants.FIRST_CONTENT_ROW_IND; row <= oldSheet.getLastRowNum(); row++){
			xssfRow = oldSheet.getRow(row);
			//有可能会有空行
			if(xssfRow == null){
				continue;
			}
			XSSFCell oldCell = xssfRow.getCell(oldCol);
			if(oldCell == null){
				continue;
			}
			XSSFRow newRow = newSheet.getRow(row);
			if(newRow == null){
				newRow = newSheet.createRow(row);
			}
			XSSFCell newCell = newRow.createCell(newCol);
			newCell.setCellStyle(contentCs);
			int oldCellType = oldCell.getCellType();
			if(XSSFCell.CELL_TYPE_NUMERIC == oldCellType){
				if(DateUtil.isCellDateFormatted(oldCell)){
					newCell.setCellValue(oldCell.getDateCellValue());
				}else{
					newCell.setCellValue(oldCell.getNumericCellValue());
				}
			}else {
				newCell.setCellValue(oldCell.getStringCellValue());
			}
		}
	}
	
	protected void generateFile(XSSFWorkbook book, String folder, String name) throws Exception{
		FileOutputStream fileOut = new FileOutputStream(folder + File.separator + name + ".xlsx");
	    book.write(fileOut);
	    fileOut.close();
	}

}
